package sf.database.jdbc.sql;

import sf.common.exception.SmallOrmException;
import sf.common.log.LogUtil;
import sf.core.DBField;
import sf.core.DBObject;
import sf.database.OrmContext;
import sf.database.OrmParameter;
import sf.database.dao.DBContext;
import sf.database.dbinfo.Feature;
import sf.database.dialect.DBDialect;
import sf.database.meta.ColumnMapping;
import sf.database.meta.MetaHolder;
import sf.database.meta.TableMapping;
import sf.database.util.OrmUtils;
import sf.database.util.OrmValueUtils;
import sf.dsl.Example;
import sf.dsl.ExampleSQLHelp;
import sf.spring.util.Assert;
import sf.spring.util.CollectionUtils;
import sf.tools.ArrayUtils;
import sf.tools.JavaTypeUtils;
import sf.tools.NumberUtils;
import sf.tools.StringUtils;

import javax.persistence.GenerationType;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 通用common
 */
public class CommonSql {

    /**
     * 设置值
     * @param pst
     * @param paras
     * @throws SQLException
     */
    public static void fillListStatement(PreparedStatement pst, List<Object> paras) throws SQLException {
        int i = 1;
        if (CollectionUtils.isEmpty(paras)) {
            return;
        }
        for (Object val : paras) {
            if (val != null) {
                val = fixBugs(val);
            }
            //setNull无需处理,jdbc会自己处理
            //pst.setNull(i++, Types.VARCHAR);
            pst.setObject(i++, val);
        }
    }

    /**
     * 设置值
     * @param pst
     * @param paras
     * @throws SQLException
     */
    public static void fillArrayStatement(PreparedStatement pst, Object... paras) throws SQLException {
        if (ArrayUtils.isEmpty(paras)) {
            return;
        }
        for (int i = 0; i < paras.length; i++) {
            Object val = paras[i];
            if (val != null) {
                val = fixBugs(val);
            }
            //setNull无需处理,jdbc会自己处理
            //pst.setNull(i + 1, Types.VARCHAR);
            pst.setObject(i + 1, val);
        }
    }

    /**
     * 设置值
     * @param pst
     * @param paras
     * @throws SQLException
     */
    public static void fillOrmStatement(PreparedStatement pst, List<OrmParameter> paras) throws SQLException {
        int i = 1;
        if (CollectionUtils.isEmpty(paras)) {
            return;
        }
        for (OrmParameter p : paras) {
            ColumnMapping cm = p.getColumnMapping();
            if (cm != null) {
                cm.getHandler().set(pst, p.getValue(), i);
            } else {
                Object val = p.getValue();
                if (val != null) {
                    val = fixBugs(val);
                }
                //setNull无需处理,jdbc会自己处理
                //pst.setNull(i, Types.VARCHAR);
                pst.setObject(i, val);
            }
            i++;
        }
    }

    /**
     * 设置值
     * @param pst
     * @param paras
     * @throws SQLException
     */
    public static void fillSQLStatement(PreparedStatement pst, List<SQLParameter> paras) throws SQLException {
        int i = 1;
        if (CollectionUtils.isEmpty(paras)) {
            return;
        }
        for (SQLParameter p : paras) {
            if (p.getHandler() != null) {
                p.getHandler().set(pst, p.getValue(), i);
            } else {
                int jdbcType = p.getJdbcType();
                //setNull无需处理,jdbc会自己处理
                //pst.setNull(i, jdbcType);
                Object val = p.getValue();
                if (val != null) {
                    val = fixBugs(val);
                }
                if (jdbcType != 0) {
                    pst.setObject(i, val, jdbcType);
                } else {
                    pst.setObject(i, val);
                }
            }
            i++;
        }
    }

    /**
     * 1.修复oracle,postgresql只认Timestamp;<br>
     * 2.枚举的处理.
     * @param val
     * @return
     */
    private static Object fixBugs(Object val) {
        if (val.getClass() == java.util.Date.class) {
            val = new Timestamp(((java.util.Date) val).getTime());
        } else if (val.getClass().isEnum()) {
            val = val.toString();
        }
        return val;
    }

    /**
     * @param table
     * @param dialect
     * @param obj
     * @param useOptimisticLock 是否使用乐观锁
     * @return
     */
    public static OrmContext model2Save(DBContext context, TableMapping table, DBDialect dialect, DBObject obj, boolean useOptimisticLock) {
        OrmContext c = new OrmContext(table);
        StringBuilder sql = new StringBuilder();
        List<OrmParameter> paras = new ArrayList<>();
        sql.append("insert into ").append(dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table))).append("(");
        List<String> cStr = new ArrayList<>();// 字段列表
        List<String> vStr = new ArrayList<>();// 值列表
        Map<DBField, Object> valueMap = obj.updateValueMap();
        for (ColumnMapping cm : table.getMetaFields()) {
            //已存在该值,则直接跳过.
            if (valueMap.containsKey(cm.getField())) {
                continue;
            }
            //sequence支持
            if (cm.getGv() != null && cm.getGv().strategy() == GenerationType.SEQUENCE && StringUtils.isNotBlank(cm.getGv().generator())) {
                if (!dialect.has(Feature.SUPPORT_SEQUENCE)) {
                    throw new RuntimeException("数据库不支持sequence");
                }
                cStr.add(dialect.wrapKeyword(cm.getRawColumnName()));
                vStr.add(dialect.getSeqNextValSql(cm.getGv().generator()));
            } else if (cm.getUniqueKeyGenerator() != null) {
                //处理自定义主键的场景
                cStr.add(dialect.wrapKeyword(cm.getRawColumnName()));
                vStr.add("?");
                paras.add(new OrmParameter(null, cm));
            } else if (cm.getGv() != null && cm.getGv().strategy() == GenerationType.AUTO && cm.getClz() == String.class) {
                //对于string设置为uuid32类型
                cStr.add(dialect.wrapKeyword(cm.getRawColumnName()));
                vStr.add("?");
                paras.add(new OrmParameter(null, cm));
            } else if (cm.getSavedDefaultValue() != null || (cm.getColumn() != null && !cm.getColumn().nullable())) {
                //字段默认值处理 SavedDefaultValue dv = cm.getSavedDefaultValue();
                //尽力了
                cStr.add(dialect.wrapKeyword(cm.getRawColumnName()));
                vStr.add("?");
                Object value = OrmValueUtils.getValue(obj, cm);
                paras.add(new OrmParameter(value, cm));
            }
        }
        //处理值
        for (Map.Entry<DBField, Object> e : valueMap.entrySet()) {
            DBField colName = e.getKey();
            ColumnMapping column = table.getSchemaMap().get(colName);
            if (column != null && !column.isNotInsert()) {
                cStr.add(dialect.wrapKeyword(column.getRawColumnName()));
                vStr.add("?");
                paras.add(new OrmParameter(e.getValue(), column));
            }
        }
        if (useOptimisticLock) {
            //乐观锁插入处理
            optimisticLockInsert(context, obj, dialect, paras, cStr, vStr);
        }
        sql.append(StringUtils.join(cStr, ",")).append(") values(").append(StringUtils.join(vStr, ",")).append(")");
        c.setSql(sql.toString());
        c.setParas(paras);
        if (paras.isEmpty()) {
            throw new SmallOrmException("no values need to be saved");
        }
        return c;
    }

    /**
     * 乐观锁插入处理
     * @param obj
     * @param paras
     * @param cStr
     * @param vStr
     */
    private static void optimisticLockInsert(DBContext context, DBObject obj, DBDialect dialect, List<OrmParameter> paras, List<String> cStr, List<String> vStr) {
        TableMapping table = MetaHolder.getMeta(obj.getClass());
        if (!table.getVersionMap().isEmpty()) {
            for (Map.Entry<DBField, ColumnMapping> e : table.getVersionMap().entrySet()) {
                ColumnMapping cm = e.getValue();
                String columnName = cm.getRawColumnName();
                Object value = obj.updateValueMap().get(e.getKey());
                if (value == null) {
                    if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                        //数字,则版本加1
                        value = 0;
                    } else if (cm.getClz() == String.class) {
                        //字符串,则使用UUID
                        value = StringUtils.uuid32();
                    } else if (Date.class.isAssignableFrom(cm.getClz())) {
                        //日期 则使用当前日期
                        value = new Date();
                    } else {
                        throw new UnsupportedOperationException("乐观锁:" + e.getKey().getClass() + " 设置有误");
                    }
                    //回写值
                    OrmValueUtils.setValue(obj, cm, value);
                    //设置参数
                    cStr.add(dialect.wrapKeyword(columnName));
                    vStr.add("?");
                    paras.add(new OrmParameter(value, cm));
                }
            }
        }
    }

    /**
     * 更新对象,如果有乐观锁,会根据乐观锁条件更新,并会更新数据库中的乐观锁条件.但不会更新对象的乐观锁字段的值.
     * @param table
     * @param dialect
     * @param obj
     * @param useOptimisticLock 是否使用乐观锁
     * @param useNvl            是否使用mysql ifnull,oralce nvl,sqlserver isnull,postgresql coalesce
     * @return
     */
    public static OrmContext forModelUpdate(DBContext context, TableMapping table, DBDialect dialect, DBObject obj, boolean useOptimisticLock, boolean useNvl) {
        OrmContext c = new OrmContext(table);
        StringBuilder sql = new StringBuilder();
        List<OrmParameter> paras = new ArrayList<>();
        List<ColumnMapping> pKeys = table.getPkFields();
        Map<DBField, Object> updateValueMap = obj.updateValueMap();
        //检查是否需要更新.
        if (updateValueMap.isEmpty()) {
            LogUtil.warn("没有需要更新的值");
            return c;
        }
        //检查乐观锁前置条件
        if (useOptimisticLock) {
            if (!obj.hasQuery() && !table.getVersionMap().isEmpty()) {
                for (Map.Entry<DBField, ColumnMapping> e : table.getVersionMap().entrySet()) {
                    if (!updateValueMap.containsKey(e.getKey())) {
                        throw new SmallOrmException("乐观锁未设置:" + e.getKey().getClass());
                    }
                }
            }
        }
        String tableName = null;
        if (obj.hasQuery()) {
            Example example = obj.useQuery();
            example.setDialect(dialect);
            tableName = ExampleSQLHelp.getFromClause(obj.useQuery(), context, false);
        } else {
            tableName = dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table));
        }
        sql.append("update ").append(tableName).append(" set ");
        boolean setStrLink = false;
        for (Map.Entry<DBField, Object> e : updateValueMap.entrySet()) {
            DBField dbField = e.getKey();
            ColumnMapping cm = table.getSchemaMap().get(dbField);
            //判断是否是主键
            boolean isPk = cm.isPk();

            //判断是否是乐观锁,如果是,则忽略.
            boolean version = useOptimisticLock && cm.isVersion();
            //必须包含该字段,且不为主键,不是乐观锁字段.
            if (!cm.isNotUpdate() && (obj.hasQuery() || !isPk) && !version) {
                sql.append(setStrLink ? "," : "").append(dialect.wrapKeyword(cm.getRawColumnName())).append("=")
                        .append(useNvl ? dialect.getNvlFunction("?", dialect.wrapKeyword(cm.getRawColumnName())) : " ? ");
                setStrLink = true;
                paras.add(new OrmParameter(e.getValue(), cm));
            }
        }
        if (useOptimisticLock) {
            //乐观锁处理
            //FIXME 未考虑返回值的情况.考虑到异常情况下的重试,对更新方法不设置对象为更新后的乐观锁比较好.此处当异常时,可以重试.
            if (!table.getVersionMap().isEmpty()) {
                //记录变更后的值
                List<OrmParameter> preResultPara = new ArrayList<>();
                for (Map.Entry<DBField, ColumnMapping> e : table.getVersionMap().entrySet()) {
                    ColumnMapping cm = e.getValue();
                    String columnName = cm.getRawColumnName();
                    Object value = updateValueMap.get(e.getKey());
                    if (JavaTypeUtils.isNumberClass(cm.getClz())) {
                        //数字,则版本加1
                        sql.append(setStrLink ? "," : "").append(dialect.wrapKeyword(columnName)).append("=").append(dialect.wrapKeyword(columnName)).append("+1");
                        setStrLink = true;
                        Object preValue = ((Number) value).longValue() + 1;
                        OrmParameter op = new OrmParameter(NumberUtils.getTargetNumber((Number) preValue, cm.getClz()), cm);
                        preResultPara.add(op);
                    } else if (cm.getClz() == String.class) {
                        //字符串,则使用UUID
                        String uuid = StringUtils.uuid32();
                        sql.append(setStrLink ? "," : "").append(dialect.wrapKeyword(columnName)).append("='").append(uuid).append("'");
                        setStrLink = true;
                        OrmParameter op = new OrmParameter(uuid, cm);
                        preResultPara.add(op);
                    } else if (Date.class.isAssignableFrom(cm.getClz())) {
                        //日期 则使用当前日期
                        sql.append(setStrLink ? "," : "").append(dialect.wrapKeyword(columnName)).append("=?");
                        setStrLink = true;
                        OrmParameter op = new OrmParameter(new Date(), cm);
                        paras.add(op);
                        preResultPara.add(op);
                    }
                }
                //设置处理后的返回值
                c.setPreResultParas(preResultPara);
            }
        }
        StringBuilder whereSql = new StringBuilder();
        boolean whereStrLink = false;
        if (obj.hasQuery()) {
            Example example = obj.useQuery();
//            example.setDialect(dialect);
            OrmContext sub = ExampleSQLHelp.getOrmContextWhereClause(example, false);
            whereSql.append(sub.getSql());
            paras.addAll(sub.getParas());
            if (whereSql.length() > 0) {
                whereStrLink = true;
            }
        } else {
            for (ColumnMapping pk : pKeys) {
                whereSql.append(whereStrLink ? " and " : "").append(dialect.wrapKeyword(pk.getRawColumnName())).append(" = ?");
                whereStrLink = true;
                Object o = null;
                if (!updateValueMap.isEmpty()) {
                    o = updateValueMap.get(pk.getField());
                }
                if (o == null) {
                    o = OrmValueUtils.getValue(obj, pk);
                }
                if (o == null) {
//                    LogUtil.warn("更新缺少主键条件!");
                    throw new SmallOrmException("更新缺少主键条件!");
//                    return c;
                }
                paras.add(new OrmParameter(o, pk));
            }
        }
        if (useOptimisticLock) {
            //乐观锁设置where值
            if (!table.getVersionMap().isEmpty()) {
                for (Map.Entry<DBField, ColumnMapping> e : table.getVersionMap().entrySet()) {
                    ColumnMapping cm = e.getValue();
                    Object value = updateValueMap.get(e.getKey());
                    String columnName = cm.getRawColumnName();
                    whereSql.append(whereStrLink ? " and " : "").append(dialect.wrapKeyword(columnName)).append(" = ?");
                    whereStrLink = true;
                    paras.add(new OrmParameter(value, cm));
                }
            }
        }
        //检查是否需要更新.
        if (!setStrLink) {
            throw new SmallOrmException("no value to be updated");
        }
        if (whereStrLink) {
            sql.append(" where ").append(whereSql);
        }
        c.setSql(sql.toString());
        c.setParas(paras);
        return c;
    }

    public static OrmContext forModelDeleteByIds(DBContext context, TableMapping table, DBDialect dialect, Object... keyParams) {
        OrmContext c = new OrmContext(table);
        List<OrmParameter> paras = new ArrayList<>();
        List<ColumnMapping> pKeys = table.getPkFields();
        StringBuilder sql = new StringBuilder("delete from ").append(dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table)));
        Assert.notEmpty(pKeys, "主键为空!");
        Assert.isTrue(keyParams != null, "主键参数为空!");
        Assert.isTrue(pKeys.size() == keyParams.length, "主键和传入的参数个数不一致!");
        for (int i = 0; i < pKeys.size(); i++) {
            sql.append(i > 0 ? " and " : " where ").append(dialect.wrapKeyword(pKeys.get(i).getRawColumnName())).append(" = ?");
            Object o = keyParams[i];
            paras.add(new OrmParameter(o, pKeys.get(i)));
        }
        c.setSql(sql.toString());
        c.setParas(paras);
        return c;
    }

    public static OrmContext forModelDelete(DBContext context, TableMapping table, DBDialect dialect, DBObject obj) {
        OrmContext c = new OrmContext(table);
        List<OrmParameter> paras = new ArrayList<>();
        List<ColumnMapping> pKeys = table.getPkFields();
        String tableName = null;
        if (obj.hasQuery()) {
            Example example = obj.useQuery();
            example.setDialect(dialect);
            tableName = ExampleSQLHelp.getFromClause(obj.useQuery(), context, false);
        } else {
            tableName = dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table));
        }
        StringBuilder sql = new StringBuilder("delete from ").append(tableName);
        StringBuilder where = new StringBuilder();
        Map<DBField, Object> valueMap = obj.updateValueMap();
        boolean existPK = true;
        for (ColumnMapping cm : pKeys) {
            if (!valueMap.containsKey(cm.getField())) {
                Object o = OrmValueUtils.getValue(obj, cm);
                if (o == null || (cm.getClz().isPrimitive()
                        && Objects.equals(o, JavaTypeUtils.getClassPrimitiveDefaultValue(cm.getClz()))/*且不等于默认值*/)) {
                    existPK = false;
                }
            }
        }
        if (obj.hasQuery()) {
            Example example = obj.useQuery();
//            example.setDialect(dialect);
            OrmContext sub = ExampleSQLHelp.getOrmContextWhereClause(example, false);
            where.append(sub.getSql());
            paras = sub.getParas();
        } else if (existPK || valueMap.isEmpty()) {
            boolean f = false;
            for (ColumnMapping cm : pKeys) {
                Object o = OrmValueUtils.getValue(obj, cm);
                if (o != null) {
                    where.append(f ? " and " : "").append(dialect.wrapKeyword(cm.getRawColumnName())).append(" = ?");
                    paras.add(new OrmParameter(o, cm));
                    f = true;
                } else {
                    throw new SmallOrmException("删除时主键为空!");
                }
            }
        } else {
            boolean f = false;
            for (Map.Entry<DBField, Object> entry : valueMap.entrySet()) {
                where.append(f ? " and " : "").append(dialect.wrapKeyword(table.getSchemaMap().get(entry.getKey()).getRawColumnName())).append(" = ?");
                Object o = entry.getValue();
                paras.add(new OrmParameter(o, table.getSchemaMap().get(entry.getKey())));
                f = true;
            }
        }
        if (where.length() > 0) {
            sql.append(" where ").append(where);
        }
        c.setSql(sql.toString());
        c.setParas(paras);
        return c;
    }

    public static OrmContext forModelSelectByIds(DBContext context, TableMapping table, DBDialect dialect, Object... keyParams) {
        OrmContext c = new OrmContext(table);
        List<ColumnMapping> pKeys = table.getPkFields();
        List<OrmParameter> parameters = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select * from ").append(dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table)));
        for (int i = 0; i < pKeys.size(); i++) {
            ColumnMapping pk = pKeys.get(i);
            sql.append(i > 0 ? " and " : " where ").append(dialect.wrapKeyword(pk.getRawColumnName())).append(" =? ");
            parameters.add(new OrmParameter(keyParams[i], pk));
        }
        c.setSql(sql.toString());
        c.setParas(parameters);
        return c;
    }

    public static OrmContext forModelExistsByIds(DBContext context, TableMapping table, DBDialect dialect, Object... keyParams) {
        OrmContext c = new OrmContext(table);
        List<ColumnMapping> pKeys = table.getPkFields();
        List<OrmParameter> parameters = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select count(1) from ").append(dialect.wrapKeyword(OrmUtils.getDynamicTableName(context, table)));
        for (int i = 0; i < pKeys.size(); i++) {
            ColumnMapping pk = pKeys.get(i);
            sql.append(i > 0 ? " and " : " where ").append(dialect.wrapKeyword(pk.getRawColumnName())).append(" =? ");
            parameters.add(new OrmParameter(keyParams[i], pk));
        }
        c.setSql(dialect.sqlPageList(sql.toString(), 0, 1));
        c.setParas(parameters);
        return c;
    }
}
